﻿using MySql.Data.MySqlClient;
using Net.Sz.Framework.Szlog;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Net.Sz.Framework.DB
{

    /// <summary>
    /// 
    /// <para>PS:</para>
    /// <para>@author 失足程序员</para>
    /// <para>@Blog http://www.cnblogs.com/shizuchengxuyuan/</para>
    /// <para>@mail 492794628@qq.com</para>
    /// <para>@phone 13882122019</para>
    /// </summary>
    public class MysqlImpl : Dal
    {

        private static SzLogger log = SzLogger.getLogger();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbUrl"></param>
        /// <param name="dbPort"></param>
        /// <param name="dbName"></param>
        /// <param name="dbUser"></param>
        /// <param name="dbPwd"></param>
        /// <param name="showSql"></param>
        public MysqlImpl(String dbUrl, String dbPort, String dbName, String dbUser, String dbPwd, bool showSql)
            : base(dbUrl, dbPort, dbName, dbUser, dbPwd, showSql)
        {

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public override DbConnection GetConnection()
        {
            MySqlConnection connection = new MySqlConnection("Server=" + this.GetDbUrl() + ";port=" + this.GetDbPort() + ";Database=" + this.GetDbName() + ";user id=" + this.GetDbUser() + ";Password=" + this.GetDbPwd() + ";persist security info=False;Charset=utf8");            
            connection.Open();
            return connection;
        }

        protected override void CreateTable(DbConnection con, DBCache cache)
        {
            String sqls = null;
            if (ExistsTable(con, cache.TableName, null))
            {
                //执行对应的创建表操作
                foreach (var column in cache.Columns)
                {
                    if (ExistsColumn(con, cache.TableName, column.ColumnName))
                    {
                        if (this.IsShowSql())
                        {
                            log.Error("表：" + cache.TableName + " 字段：" + column.Name + " 映射数据库字段：" + column.ColumnName + " 存在，将不会修改，");
                        }
                        /*   String sqls = "ALTER TABLE " + tableName + " CHANGE `" + key + "` " + value.getValue() + ";";
                                    if (showSql) {
                                        log.error("执行语句：" + sqls);
                                    }
                                    try (Statement cs1 = con.createStatement()) {
                                        boolean execute1 = cs1.execute(sqls);
                                        if (showSql) {
                                            log.error("执行结果：" + execute1);
                                        }
                                    }*/
                    }
                    else
                    {
                        sqls = "ALTER TABLE `" + cache.TableName + "` ADD `" + column.Name + "` " + column.DBType + ";\n";
                        this.ExecuteQuery(con, sqls);
                        if (column.IsUnique)
                        {
                            sqls = "ALTER TABLE `" + cache.TableName + "` ADD " + "Unique Key un_key_" + cache.TableName + "_" + column.Name + " (" + "`" + column.Name + "`) USING HASH ;\n";
                            this.ExecuteQuery(con, sqls);
                        }
                        else if (column.IsIndex)
                        {
                            sqls = "ALTER TABLE `" + cache.TableName + "` ADD " + "INDEX in_key_" + cache.TableName + "_" + column.Name + " (" + "`" + column.Name + "`) USING HASH ;\n";
                            this.ExecuteQuery(con, sqls);
                        }
                    }
                }
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("\n create table if not exists `").Append(cache.TableName).Append("` (\n");
                //将所有的字段拼接成对应的SQL语句
                foreach (var column in cache.Columns)
                {
                    sb.Append("     `").Append(column.Name).Append("` ");
                    sb.Append(column.DBType);
                    sb.Append(" COMMENT '").Append(column.Description).Append("' ");
                    sb.Append(",\n");
                }

                sb.Append("     PRIMARY KEY p_key_").Append(cache.TableName).Append(" (");

                int pi = 0;
                foreach (var column in cache.Columns)
                {
                    if (column.IsP)
                    {
                        if (pi > 0)
                        {
                            sb.Append(", ");
                        }
                        sb.Append("`").Append(column.Name).Append("`");
                        pi++;
                    }
                }

                sb.Append(")");
                sb.Append("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                //执行对应的创建表操作
                sqls = sb.ToString();
                this.ExecuteQuery(con, sqls);

                foreach (var column in cache.Columns)
                {
                    if (column.IsUnique)
                    {
                        sqls = "ALTER TABLE `" + cache.TableName + "` ADD " + "Unique Key un_key_" + cache.TableName + "_" + column.Name + " (" + "`" + column.Name + "`) USING HASH ;\n";
                        this.ExecuteQuery(con, sqls);
                    }
                    else if (column.IsIndex)
                    {
                        sqls = "ALTER TABLE `" + cache.TableName + "` ADD " + "INDEX in_key_" + cache.TableName + "_" + column.Name + " (" + "`" + column.Name + "`) USING HASH ;\n";
                        this.ExecuteQuery(con, sqls);
                    }
                }

                if (this.IsShowSql())
                {
                    log.Error("\n表：" + cache.TableName + "\n 创建完成；");
                }
            }
        }

        private static readonly String ifexitscolumn = "SELECT sum(1) usm FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA= ? AND table_name= ? AND COLUMN_NAME = ? ;";
        private static readonly String ifexitstable = "select sum(1) usm from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`= ? and `TABLE_NAME`= ? ;";

        protected override bool ExistsTable(DbConnection con, string tableName, DBCache dbCache)
        {
            if (this.ExecuteScalar<Int32>(con, ifexitstable, this.GetDbName(), tableName) > 0)
            {
                if (this.IsShowSql())
                {
                    log.Error("数据库：" + this.GetDbName() + " 表：" + tableName + " 检查结果：已存在");
                }
                if (dbCache != null && dbCache.Columns.Count > 0)
                {
                    foreach (var item in dbCache.Columns)
                    {
                        if (ExistsColumn(con, tableName, item.ColumnName))
                        {
                            if (this.IsShowSql())
                            {
                                log.Error("数据库：" + this.GetDbName() + " 表：" + tableName + " 映射数据库字段：" + item.ColumnName + " 检查结果：已存在，将不会修改");
                            }
                        }
                        else
                        {
                            throw new Exception("数据库：" + this.GetDbName() + " 表：" + tableName + " 字段：" + item.ColumnName + " 检查结果：无此字段 ");
                        }
                    }
                }
                return true;
            }
            else
            {
                if (this.IsShowSql())
                {
                    log.Error("数据库：" + this.GetDbName() + " 表：" + tableName + " 检查结果：无此表 ");
                }
                return false;
            }
        }

        protected override bool ExistsColumn(DbConnection con, string tableName, string columnName)
        {
            if (this.ExecuteScalar<Int32>(con, ifexitscolumn, this.GetDbName(), tableName, columnName) > 0) { return true; }
            else { return false; }
        }

        protected override void SetStmtParam(DbParameterCollection stmt, object value)
        {
            ((MySqlParameterCollection)stmt).AddWithValue(string.Empty, value);
        }
    }
}
